/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.boun.cmpe.elele.cotrans.dbOperations;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ppt
 */
public class DbOperations {

    Connection conn;

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public DbOperations() {
        this.conn = new ConnectDB().getConn();
    }

    public boolean checkLogin(String uname, String pass) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT * FROM `UserInfo` WHERE userName='" + uname + "' AND password='" + pass + "' ";
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public Vector getUsersActiveTranslations(String uname) {
        Vector<String> data = new Vector<String>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs;
            String query = "SELECT TranslationInfo.path FROM TranslationInfo,"
                    + "UserTranslation,UserInfo WHERE UserInfo.userName='"
                    + uname + "' AND UserTranslation.userID=UserInfo.userID AND TranslationInfo.transID=UserTranslation.transID"
                    + " AND TranslationInfo.isFinished=0";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                data.add(rs.getString(1));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }

        return data;
    }

    public Vector getUsersFinishedTranslations(String uname) {
        Vector<String> data = new Vector<String>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs;
            String query = "SELECT TranslationInfo.path FROM TranslationInfo,"
                    + "UserTranslation,UserInfo WHERE UserInfo.userName='"
                    + uname + "' AND UserTranslation.userID=UserInfo.userID AND TranslationInfo.transID=UserTranslation.transID"
                    + " AND TranslationInfo.isFinished=1";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                data.add(rs.getString(1));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }

    public Vector getAllActiveTranslations() {
        Vector<String> data = new Vector<String>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs;
            String query = "SELECT TranslationInfo.path FROM TranslationInfo"
                    + " WHERE TranslationInfo.isFinished=0";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                data.add(rs.getString(1));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }

    public Vector getAllFinishedTranslations() {
        Vector<String> data = new Vector<String>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs;
            String query = "SELECT TranslationInfo.path FROM TranslationInfo"
                    + " WHERE TranslationInfo.isFinished=1";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                data.add(rs.getString(1));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }

        return data;
    }

    public Vector getReceivedPendingInvitations(int uID) {
        Vector<String> data = new Vector<String>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs;
            String query = "SELECT InvitationInfo.invitationID, UserInfo.userName, InvitationInfo.invitationDate, TranslationInfo.path"
                    + " FROM InvitationInfo "
                    + "INNER JOIN UserInfo "
                    + "ON UserInfo.userID=InvitationInfo.fromUserID "
                    + "INNER JOIN TranslationInfo "
                    + "ON TranslationInfo.transID=InvitationInfo.transID "
                    + " WHERE "
                    + " InvitationInfo.toUserID=" + uID + " AND InvitationInfo.isAccepted=0"
                    + " AND InvitationInfo.invitationDate=InvitationInfo.responseDate"
                    + " ORDER BY InvitationInfo.invitationDate ";
            System.out.println(query);
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                data.add(Integer.toString(rs.getInt(1))); //Invitation ID
                data.add(rs.getString(2)); //userName
                data.add(((Date) rs.getObject(3)).toString()); //Date
                data.add(rs.getString(4)); //path
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }

    public Integer getUserID(String uname, String pass) {
        Integer id = 0;
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT * FROM UserInfo WHERE userName='" + uname + "' AND password='" + pass + "' ";
            rs = stmt.executeQuery(query);
            rs.next();
            id = rs.getInt(1);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }

        return id;
    }

    public Integer getUserID(String uname) {
        Integer id = 0;
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT * FROM UserInfo WHERE userName='" + uname + "'";
            rs = stmt.executeQuery(query);
            rs.next();
            id = rs.getInt(1);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }

        return id;
    }

    public Integer insertDocument(String docUrl, String docPath, int isRet) {
        Integer insertedKeyValue = 0;
        try {
            Statement stmt = (Statement) conn.createStatement();
            String query = "INSERT INTO DocumentInfo(documentID,documentURL,documentPath,isRetrieved) values (0,'" + docUrl + "','" + docPath + "'," + isRet + ")";
            //System.out.println(query);
            stmt.executeUpdate(query);
            ResultSet rs = stmt.getGeneratedKeys();
            rs.next();
            insertedKeyValue = rs.getInt(1);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }

        return insertedKeyValue;
    }

    public Integer insertTranslation(String path, Integer documentID, Integer isLocked, Integer isFinished) {
        Integer insertedKeyValue = 0;
        try {
            Statement stmt = (Statement) conn.createStatement();
            String query = "INSERT INTO TranslationInfo(transID,path,documentID,isLocked,isFinished) values (0,'" + path + "'," + documentID + "," + isLocked + "," + isFinished + ")";
            stmt.executeUpdate(query);
            ResultSet rs = stmt.getGeneratedKeys();
            rs.next();
            insertedKeyValue = rs.getInt(1);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }

        return insertedKeyValue;
    }

    public ArrayList<String> getAllUsers() {
        ArrayList<String> res = new ArrayList<String>();
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT userName FROM UserInfo";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                String val = rs.getString(1);
                res.add(val);
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    public ArrayList<String> getTransDoc(String transID) {//ilk param trans path, ikinci param doc path
        ArrayList<String> res = new ArrayList<String>();
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT transID, path, TranslationInfo.documentID, documentPath FROM TranslationInfo"
                    + " INNER JOIN DocumentInfo ON TranslationInfo.documentID=DocumentInfo.documentID"
                    + " WHERE transID=" + transID;
            rs = stmt.executeQuery(query);
            rs.next();
            String transPath = rs.getString(2);
            res.add(transPath);
            String docPath = rs.getString(4);
            res.add(docPath);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    public void insertRating(String transID, String userID, String rate) {

        try {
            Statement stmt = (Statement) conn.createStatement();
            String query = "INSERT INTO UserRatesTranslation(transID,userID,rate) values ('" + transID + "'," + userID + "," + rate + ")";
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public ArrayList<String> getTranslationRate(String transID) {//1. param people, 2. param rate
        ArrayList<String> res = new ArrayList<String>();
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT * FROM TranslationRate where transID=" + transID;
            rs = stmt.executeQuery(query);
            String noPeople = "";
            String rate = "";

            while (rs.next()) {
                noPeople = rs.getString(2);
                rate = rs.getString(3);
            }

            if (noPeople != "" && rate != "") {
                res.add(noPeople);
                res.add(rate);
            }

            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    public void updateTranslationRate(String transID, String people, String rate) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            String query = "UPDATE TranslationRate SET numberOfPeopleRated=" + people + ",rate=" + rate + " where transID=" + transID;
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insertTranslationRate(String transID, String people, String rate) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            String query = "INSERT INTO TranslationRate(transID,numberOfPeopleRated,Rate) VALUES(" + transID + "," + people + "," + rate + ")";
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insertUserTranslation(String userID, String transID) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            java.util.Date now = new java.util.Date();
            String query = "INSERT INTO UserTranslation(userID,transID,isDone,lastLogin) VALUES(" + userID + "," + transID + ",0,'" + new java.sql.Date(now.getTime()) + "')";
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insertUserRate(String userID, String people, String rate) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            java.util.Date now = new java.util.Date();
            String query = "INSERT INTO UserRate(userID,numberOfPeopleRated,Rate) VALUES(" + userID + "," + people + "," + rate + ")";
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateUserRate(String userID, String people, String rate) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            String query = "UPDATE UserRate SET numberOfPeopleRated=" + people + ",rate=" + rate + " where userID=" + userID;
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<String> getUserRate(String userID) {//1. param people, 2. param rate
        ArrayList<String> res = new ArrayList<String>();
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT * FROM UserRate where userID=" + userID;
            rs = stmt.executeQuery(query);
            String noPeople = "";
            String rate = "";

            while (rs.next()) {
                noPeople = rs.getString(2);
                rate = rs.getString(3);
            }

            if (noPeople != "" && rate != "") {
                res.add(noPeople);
                res.add(rate);
            }

            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    public ArrayList<String> getUsersOnTrans(String transID) {
        ArrayList<String> res = new ArrayList<String>();
        ResultSet rs = null;
        try {
            Statement stmt = (Statement) conn.createStatement();

            String query = "SELECT * FROM UserTranslation WHERE transID=" + transID;
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                res.add(rs.getString(1));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    public String getUserMail(String uname) {
        String s = "";
        try {
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs;
            String query = "SELECT email FROM UserInfo WHERE userName='" + uname + "'";
            rs = stmt.executeQuery(query);
            rs.next();
            s = rs.getString(1);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return s;
    }

    public void insertInvitationInfo(String fromUserID, String toUserID, String transID) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            java.util.Date now = new java.util.Date();
            String query = "insert into InvitationInfo (fromUserID,toUserID,transID,invitationDate,responseDate,isAccepted) values ('" + fromUserID + "','" + toUserID + "','" + transID + "','" + new java.sql.Date(now.getTime()) + "','" + new java.sql.Date(now.getTime()) + "','0')";
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean acceptInvitation(int invID) {
        try {
            Statement stmt = (Statement) conn.createStatement();
           java.util.Date now = new java.util.Date();
            String sql="UPDATE InvitationInfo SET isAccepted=1, responseDate='"
                    +new java.sql.Date(now.getTime())+"' WHERE invitationID="+invID;
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    
        public boolean rejectInvitation(int invID) {
        try {
            Statement stmt = (Statement) conn.createStatement();
            java.util.Date now = new java.util.Date();
            String sql="UPDATE InvitationInfo SET isAccepted=2, responseDate='"
                    +new java.sql.Date(now.getTime())+"' WHERE invitationID="+invID;
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbOperations.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    } 
}
